package com.cc.utils.poi;

import com.cc.utils.poi.UtilDao.AutoWith;
import com.cc.utils.poi.dao.excellbasic.ColumnCell;
import com.cc.utils.poi.dao.excellbasic.ExcellCellBasics;
import com.cc.utils.poi.dao.excellbasic.ExcellSheetBasic;
import com.cc.utils.poi.dao.excellbasic.RowCell;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import static org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN;
import static org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA;
import static org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC;


/**
 * @User c.c
 * @Since 2022/8/12
 * @Time 8:38
 * @Description 生成Excel模板/导出Excell
 */

public class ExcelPoiUtils {


  /**
   * 通用生成excell 通用模板
   *
   * @param sheetName sheet名称
   * @param title     标题
   * @param content   内容
   * @param wb        HSSFWorkbook对象
   * @return HSSFWorkbook
   */
  @Deprecated
  public static HSSFWorkbook genericTemplate(String sheetName, String[] title, String[][] content, HSSFWorkbook wb) {

    // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
    if (wb == null) {
      wb = new HSSFWorkbook();
    }
    // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
    HSSFSheet sheet = wb.createSheet(sheetName);

    // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
    HSSFRow row = ExcellPoiStyle.creatRow(sheet, 0, 25);


    //设置表头
    for (int i = 0; i < title.length; i++) {
      //设置列
      HSSFCell cell = ExcellPoiStyle.creatCell(row, i, title[i]);
      ExcellPoiStyle.fontStyle(wb, cell, "宋体", 12, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_TOP);
      ExcellPoiStyle.borderStyle(cell, HSSFCellStyle.BORDER_THIN);
    }

    //设置内容
    for (int i = 0; i < content.length; i++) {
      //设置排
      row = ExcellPoiStyle.creatRow(sheet, i + 1, 0);
      for (int j = 0; j < content[i].length; j++) {
        //将内容按顺序赋给对应的列对象
        HSSFCell cell = ExcellPoiStyle.creatCell(row, j, content[i][j]);
        ExcellPoiStyle.fontStyle(wb, cell);
        ExcellPoiStyle.borderStyle(cell, HSSFCellStyle.BORDER_THIN);
      }
    }

    //应用样式
    ExcellPoiStyle.applystyle();

    //让列宽随着导出的列长自动适应
    ExcellPoiStyle.AutoExcellWidth(title, sheet);

    return wb;
  }


  public static void outputExcell(HSSFWorkbook wb, String fileAddress, String excellName) throws IOException {
    String target = fileAddress + "/" + excellName;
    FileOutputStream out = new FileOutputStream(target);
    wb.write(out);
    out.flush();
    out.close();
  }


  /**
   * @param workBook 工作簿基础数据
   * @return HSSFWorkbook<T>
   * @author C.c
   * @since 2022-12-16 11:38
   **/

  public static <T> HSSFWorkbook creatExcell(ExcellSheetBasic<T> workBook) {
    return creatExcell(workBook, new AutoWith(false, 0), false);
  }

  public static <T> HSSFWorkbook creatExcell(ExcellSheetBasic<T> workBook, boolean isLine) {
    return creatExcell(workBook, new AutoWith(false, 0), isLine);
  }


  /**
   * @param workBook 工作簿基础数据
   * @param autoWith 是否启用自动列宽
   * @param isLine   是否画边框
   * @return HSSFWorkbook<T>
   * @author C.c
   * @since 2022-12-21 12:06
   **/
  public static <T> HSSFWorkbook creatExcell(ExcellSheetBasic<T> workBook, AutoWith autoWith, boolean isLine) {


    ArrayList<CellRangeAddress> cellRangeAddresses = new ArrayList<>();
    HashMap<HSSFCell, ExcellCellBasics<T>> map = new HashMap<>();
    HSSFWorkbook wb = workBook.getWorkbook();
    //TODO 多表暂时不支持
    HSSFSheet sheet = wb.getSheetAt(0);
    //获取每个单元格样式
    List<ExcellCellBasics<T>> cellList = workBook.getExcellCellBasics().getCellList();
    //获取样式
    HSSFCellStyle basiccellStyle = workBook.getExcellCellBasics().getCellStyle();

    //划分表格
    cellList.forEach(excellBasicsCell -> {
      CellRangeAddress cellRangeAddress = new CellRangeAddress(
        excellBasicsCell.getRowBasicCell().getStart(),
        excellBasicsCell.getRowBasicCell().getEnd(),
        excellBasicsCell.getColumnCell().getStart(),
        excellBasicsCell.getColumnCell().getEnd()
      );
      sheet.addMergedRegion(cellRangeAddress);
      cellRangeAddresses.add(cellRangeAddress);
    });

    //生成单位格
    cellList.forEach(excellBasicsCell -> {
      HSSFRow row;
      if (Objects.nonNull(excellBasicsCell.getHssfCell())) {
        row = excellBasicsCell.getHssfCell().getRow();
      } else {
        row = sheet.createRow(excellBasicsCell.getRowBasicCell().getStart());
      }
      HSSFCell cell = row.createCell(excellBasicsCell.getColumnCell().getStart());
//      cell.setCellStyle(cellStyle);

      map.put(cell, excellBasicsCell);
    });


    //划线
    cellRangeAddresses.forEach(cellRangeAddress -> {

      if (Objects.nonNull(basiccellStyle)) {
        RegionUtil.setBorderBottom(basiccellStyle.getBorderBottom(), cellRangeAddress, sheet, wb);
        RegionUtil.setBorderLeft(basiccellStyle.getBorderLeft(), cellRangeAddress, sheet, wb);
        RegionUtil.setBorderRight(basiccellStyle.getBorderRight(), cellRangeAddress, sheet, wb);
        RegionUtil.setBorderTop(basiccellStyle.getBorderTop(), cellRangeAddress, sheet, wb);
      } else if (!isLine) {
        //默认为空线
        RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_NONE, cellRangeAddress, sheet, wb);
        RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_NONE, cellRangeAddress, sheet, wb);
        RegionUtil.setBorderRight(HSSFCellStyle.BORDER_NONE, cellRangeAddress, sheet, wb);
        RegionUtil.setBorderTop(HSSFCellStyle.BORDER_NONE, cellRangeAddress, sheet, wb);
      } else {
        //若划线就是实线
        RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, cellRangeAddress, sheet, wb);
        RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, cellRangeAddress, sheet, wb);
        RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, cellRangeAddress, sheet, wb);
        RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, cellRangeAddress, sheet, wb);
      }
    });


    // 设置内容 注:一点要最后设置内容和样式，顺序不能变
    for (Map.Entry<HSSFCell, ExcellCellBasics<T>> hssfCellStringEntry : map.entrySet()) {
      //设置值
      hssfCellStringEntry.getKey()
        .setCellValue(hssfCellStringEntry.getValue().getVaule() == null ? null : hssfCellStringEntry.getValue().getVaule().toString());
      //判断样式是否为空
      if (Objects.nonNull(hssfCellStringEntry.getValue().getCellStyle())) {
        //判断是否需要划线
        if (isLine) {
          hssfCellStringEntry.getValue().getCellStyle().setBorderLeft(HSSFCellStyle.BORDER_THIN);
          hssfCellStringEntry.getValue().getCellStyle().setBorderRight(HSSFCellStyle.BORDER_THIN);
          hssfCellStringEntry.getValue().getCellStyle().setBorderTop(HSSFCellStyle.BORDER_THIN);
          hssfCellStringEntry.getValue().getCellStyle().setBorderBottom(HSSFCellStyle.BORDER_THIN);
        }
        hssfCellStringEntry.getKey().setCellStyle(hssfCellStringEntry.getValue().getCellStyle());
//        HSSFCellStyle cellStyle1 = hssfCellStringEntry.getValue().getCellStyle();
//        hssfCellStringEntry.getKey().getCellStyle().cloneStyleFrom(cellStyle1);
      } else {
        if (isLine) {
          HSSFCellStyle cellStyle = wb.createCellStyle();
          cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
          cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
          cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          hssfCellStringEntry.getKey().setCellStyle(cellStyle);
        }
      }
    }


    if (autoWith.getIsAutoWith()) {
      //智适应列宽
      List<T> content = workBook.getExcellCellBasics().getContent(Math.max(autoWith.getIndexRow() - 1, 0));
      for (int i = 0; i < content.size(); i++) {
        int with = content.get(i).toString().getBytes().length * 2 * 160;
        sheet.setColumnWidth(i, with > 10000 ? with / 2 : with);
      }
    }


    return wb;
  }

  /**
   * @param fileAddress 模板文件
   * @return ExcellSheetBasic<Object>
   * @author C.c
   * @since 2022-12-23 09:23
   **/
  public static ExcellSheetBasic<Object> readTemplateExcell(String fileAddress) throws IOException {
    InputStream is = new FileInputStream(fileAddress);
    POIFSFileSystem fs = new POIFSFileSystem(is);
    HSSFWorkbook wb = new HSSFWorkbook(fs);

//    wb = excellSheetBasic.getWorkbook();

    HashMap<String, ExcellCellBasics<Object>> excellCellBasicsHashMap = null;
    //遍历sheet
    int numberOfSheets = wb.getNumberOfSheets();
    for (int i = 0; i < numberOfSheets; i++) {
      HSSFSheet sheetAt = wb.getSheetAt(i);

      if (sheetAt.getLastRowNum() < 1) {
        continue;
      }
      //获取合并的列
      excellCellBasicsHashMap = getMergedRegion(sheetAt);


      //遍历row
      for (int row = 0; row <= sheetAt.getLastRowNum(); row++) {
        HSSFRow r = sheetAt.getRow(row);
        if (r == null) {
          continue;
        }
        //遍历单元格
        for (int column = 0; column < r.getLastCellNum(); column++) {
          HSSFCell cell = r.getCell(column);

          if (cell == null) {
            continue;
          }

          //放置内容
          //判断单元格数据类型
          switch (cell.getCellType()) {
            case CELL_TYPE_BOOLEAN: //  布尔型 4
              //TODO
              System.out.print(cell.getBooleanCellValue() + "  ");
              break;

            case CELL_TYPE_NUMERIC: // 数值型 0
              //TODO
              System.out.print(cell.getNumericCellValue() + "  ");
              break;

            case CELL_TYPE_FORMULA: //  公式型 2
              //TODO
              System.out.print(cell.getStringCellValue() + "  ");
              break;

            default: // 其他类型则按字符串处理
              if (StringUtils.isNotBlank(cell.getStringCellValue())) {
                String key = cell.getRowIndex() + ":" + cell.getColumnIndex();
                ExcellCellBasics<Object> excellCellBasics = new ExcellCellBasics<>(
                  cell.getStringCellValue(),
                  RowCell.intiRow(row + 1),
                  ColumnCell.intiColumn(column + 1),
                  cell.getCellStyle(), null, null, cell
                );
                ExcellCellBasics<Object> temp = excellCellBasicsHashMap.putIfAbsent(key, excellCellBasics);
                if (Objects.nonNull(temp)) {
                  temp.setVaule(cell.getStringCellValue());
                  temp.setCellStyle(cell.getCellStyle());
                  temp.setHssfCell(cell);
                }
              }
              break;
          }
        }
      }

    }

    ExcellCellBasics<Object> excellCellBasics = ExcellCellBasics.getInstance();

    //初始化需要的数据
    if (excellCellBasicsHashMap != null) {
      for (ExcellCellBasics<Object> value : excellCellBasicsHashMap.values()) {
        excellCellBasics.initTitle(value.getVaule(), value.getRowBasicCell(), value.getColumnCell(), value.getCellStyle(), value.getHssfCell());
      }
    }

    ExcellSheetBasic<Object> excellSheetBasic = ExcellSheetBasic.getInstance();
    excellSheetBasic.initWorkBook(wb, excellCellBasics);
    return excellSheetBasic;
  }

  /**
   * 获取合并列宽
   *
   * @param sheet 工作簿
   * @return HashMap<String, ExcellCellBasics < T>>
   * @author C.c
   * @since 2022-12-23 08:34
   **/
  private static <T> HashMap<String, ExcellCellBasics<T>> getMergedRegion(HSSFSheet sheet) {
    HashMap<String, ExcellCellBasics<T>> excellCellBasicsHashMap = new HashMap<>();
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
      CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
      ExcellCellBasics<T> excellCellBasics = new ExcellCellBasics<>(null,
        RowCell.intiRow(mergedRegion.getFirstRow() + 1, mergedRegion.getLastRow() + 1),
        ColumnCell.intiColumn(mergedRegion.getFirstColumn() + 1, mergedRegion.getLastColumn() + 1)
      );
      excellCellBasicsHashMap.put(mergedRegion.getFirstRow() + ":" + mergedRegion.getFirstColumn(), excellCellBasics);
    }
    return excellCellBasicsHashMap;
  }
}
